package com.example.exceltosqlite.ui;

import android.content.Intent;
import android.inputmethodservice.Keyboard;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

import com.example.exceltosqlite.R;
import com.example.exceltosqlite.base.BaseActivity;
import com.example.exceltosqlite.bean.CommonBean;
import com.example.exceltosqlite.bean.MyConstant;
import com.example.exceltosqlite.dao.ExcelFileDao;
import com.example.exceltosqlite.dao.ExcelFileNumberDao;
import com.example.exceltosqlite.dao.ExcelFileTypeDao;
import com.example.exceltosqlite.entity.ExcelFileEntity;
import com.example.exceltosqlite.entity.SqliteFileEntity;
import com.example.exceltosqlite.entity.SqliteFileNumberEntity;
import com.example.exceltosqlite.entity.SqliteFileTypeEntity;
import com.example.exceltosqlite.ui.sqldao.SqliteDaoActivity;
import com.example.exceltosqlite.util.DateUtil;
import com.example.exceltosqlite.util.MyUtil;
import com.example.exceltosqlite.util.PermissionsUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.greenrobot.eventbus.EventBus;
import org.greenrobot.eventbus.Subscribe;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import butterknife.BindView;
import butterknife.ButterKnife;
import butterknife.OnClick;

public class MainActivity extends BaseActivity {

    @BindView(R.id.fileNameTV)
    TextView fileNameTV;
    @BindView(R.id.bt1)
    Button bt1;
    @BindView(R.id.bt2)
    Button bt2;
    @BindView(R.id.bt3)
    Button bt3;
    @BindView(R.id.bt4)
    Button bt4;
    private ExcelFileDao excelFileDao;
    private ExcelFileTypeDao excelFileTypeDao;
    private ExcelFileNumberDao excelFileNumberDao;
    private List<SqliteFileEntity> resultSqliteList = new ArrayList<>();
    private List<ExcelFileEntity> excelFileEntityList = new ArrayList<>();
    private String tempErrorStr = ""; //错误的格式

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        ButterKnife.bind(this);
        EventBus.getDefault().register(this);
        init();
    }


    private void init() {
        //需要请求权限 拒绝权限 回调自行处理
        PermissionsUtil.isGrantExternalRW(this, 0);
        excelFileDao = new ExcelFileDao(this);
        excelFileTypeDao = new ExcelFileTypeDao(this);
        excelFileNumberDao = new ExcelFileNumberDao(this);

    }

    @OnClick({R.id.bt1, R.id.bt2, R.id.bt3, R.id.bt4})
    public void onViewClicked(View view) {
        Intent intent;
        switch (view.getId()) {
            case R.id.bt1:
                intent = new Intent(this, FileSelectActivity.class);
                startActivity(intent);
                break;
            case R.id.bt2:
                if ("".equals(fileNameTV.getText().toString().trim())) {
                    Toast.makeText(this, "请先选择文件", Toast.LENGTH_SHORT).show();
                    return;
                }
                readExcel();
                break;
            case R.id.bt3:
                if ("".equals(fileNameTV.getText().toString().trim())) {
                    Toast.makeText(this, "请先选择文件路径，用于导出使用", Toast.LENGTH_SHORT).show();
                    return;
                }
                intent = new Intent(this, SqliteDaoActivity.class);
                intent.putExtra(MyConstant.STR_NAME_1, fileNameTV.getText().toString().trim());
                startActivity(intent);
                break;
            case R.id.bt4:
                excelFileTypeDao.deleteSql();
                excelFileNumberDao.deleteSql();
                excelFileDao.deleteSql();
                Toast.makeText(this, "数据库表已经清空", Toast.LENGTH_SHORT).show();
                break;
        }
    }


    /**
     * 将内容写入数据库
     */
    private void doWriteSqlDB(List<ExcelFileEntity> excelFileEntities) {
        try {
            //Type表添加数据  重复的不添加  去掉这个逻辑
//            List<SqliteFileTypeEntity> sqliteFileTypeEntityList = excelFileTypeDao.queryAll();
//            SqliteFileTypeEntity sqliteFileTypeEntity = new SqliteFileTypeEntity();
//            sqliteFileTypeEntity.setType(excelFileEntities.get(0).getCol00());
//            if (!sqliteFileTypeEntityList.contains(sqliteFileTypeEntity)) {
//                excelFileTypeDao.insert(sqliteFileTypeEntity);
//            }
            //number表添加数据  重复的提示 重复  类型跟编号都一样 视为重复
            List<SqliteFileNumberEntity> sqliteFileNumberEntityList = excelFileNumberDao.queryAll();
            int tempTypeId = excelFileTypeDao.queryKey(excelFileEntities.get(0).getCol00());
            if(-1 == tempTypeId){
                Toast.makeText(this, "导入失败，文件类型错误请检查", Toast.LENGTH_SHORT).show();
                return;
            }
            SqliteFileNumberEntity sqliteFileNumberEntity = new SqliteFileNumberEntity();
            sqliteFileNumberEntity.setDate(DateUtil.getNowDate());
            sqliteFileNumberEntity.setTypeID(tempTypeId);
            sqliteFileNumberEntity.setMessageNo(excelFileEntities.get(0).getCol01());
            if (!sqliteFileNumberEntityList.contains(sqliteFileNumberEntity)) {
                sqliteFileNumberEntity.setMessageName(excelFileEntities.get(0).getCol02());
                excelFileNumberDao.insert(sqliteFileNumberEntity);
            } else {
                Toast.makeText(this, "导入失败，已存在同样的类型和编号", Toast.LENGTH_SHORT).show();
                return;
            }
            //报词表添加数据
            int tempNumberKey = excelFileNumberDao.queryKey(sqliteFileNumberEntity.getTypeID(), sqliteFileNumberEntity.getMessageNo());
            excelFileDao.insertAll(changeToSqliteEntitys(tempNumberKey, excelFileEntities));
            Toast.makeText(this, "导入成功", Toast.LENGTH_SHORT).show();
        } catch (SQLException e) {
            e.printStackTrace();
            Toast.makeText(this, "导入失败" + e.getMessage(), Toast.LENGTH_SHORT).show();
        }

    }

    @Subscribe
    public void onEvent(CommonBean bean) {
        if (MyConstant.EVENT_MSG_1.equals(bean.getKey())) {
            fileNameTV.setText(bean.getValue());
        }
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        EventBus.getDefault().unregister(this);
    }


    /**
     * 将Excel读取之后的对象转换成Sqlite需要的对象
     *
     * @param excelFileEntities
     * @return
     */
    private List<SqliteFileEntity> changeToSqliteEntitys(int key, List<ExcelFileEntity> excelFileEntities) {
        resultSqliteList.clear();
        for (int i = 1; i < excelFileEntities.size(); i++) {
            SqliteFileEntity sqliteFileEntity = new SqliteFileEntity();
            sqliteFileEntity.setExcelFileEntity(key, excelFileEntities.get(i));
            resultSqliteList.add(sqliteFileEntity);
        }
        return resultSqliteList;
    }


    /**
     * 读取excel文件处理
     */
    private void readExcel() {
        excelFileEntityList.clear();
        File file = new File(fileNameTV.getText().toString());
        String filePath = file.getAbsolutePath();
        Sheet sheet = null;
        Row row = null;
        Workbook wb = null;
        if (filePath == null) {
            return;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
            if (wb != null) {
                // 获取第一个sheet
                sheet = wb.getSheetAt(0);
                // 获取最大行数
                int rows = sheet.getPhysicalNumberOfRows();
                // 获取第一行
                row = sheet.getRow(0);
                // 获取最大列数
                int cols = row.getPhysicalNumberOfCells();

                ExcelFileEntity excelFileEntity0 = new ExcelFileEntity();
                excelFileEntity0.setCol00(getCellFormatValue(sheet.getRow(0).getCell(0)));
                excelFileEntity0.setCol01(getCellFormatValue(sheet.getRow(0).getCell(1)));
                if (!MyUtil.isNumber(excelFileEntity0.getCol01())) {
                    Toast.makeText(this, "报文编号格式不正确，需要为整数数字", Toast.LENGTH_SHORT).show();
                    return;
                }
                excelFileEntity0.setCol02(getCellFormatValue(sheet.getRow(0).getCell(2)));
                excelFileEntityList.add(excelFileEntity0);
                for (int i = 1; i < rows; i++) {
                    ExcelFileEntity excelFileEntity = new ExcelFileEntity();
                    excelFileEntity.setCol00(getCellFormatValue(sheet.getRow(i).getCell(0)));
                    excelFileEntity.setCol01(getCellFormatValue(sheet.getRow(i).getCell(1)));
                    excelFileEntity.setCol02(getCellFormatValue(sheet.getRow(i).getCell(2)));
                    excelFileEntity.setCol03(getCellFormatValue(sheet.getRow(i).getCell(3)));
                    excelFileEntity.setCol04(getCellFormatValue(sheet.getRow(i).getCell(4)));
                    excelFileEntity.setCol05(getCellFormatValue(sheet.getRow(i).getCell(5)));
                    excelFileEntity.setCol06(getCellFormatValue(sheet.getRow(i).getCell(6)));
                    excelFileEntity.setCol07(getCellFormatValue(sheet.getRow(i).getCell(7)));
                    excelFileEntity.setCol08(getCellFormatValue(sheet.getRow(i).getCell(8)));
                    excelFileEntity.setCol09(getCellFormatValue(sheet.getRow(i).getCell(9)));
                    excelFileEntityList.add(excelFileEntity);
                    for (int j = 0; j < cols; j++) {
                        if (!MyUtil.isCharAndNumber(getCellFormatValue(sheet.getRow(i).getCell(j)))) {
                            tempErrorStr += "(第" + (i + 1) + "行 第" + (j + 1) + "列)";
                        }
                    }
                }
                if ("".equals(tempErrorStr)) {
                    doWriteSqlDB(excelFileEntityList);
                } else {
                    Toast.makeText(this, "导入失败，报词存在非法字符: " + tempErrorStr, Toast.LENGTH_SHORT).show();
                }

            }
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    public static String getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = (cell.getNumericCellValue() + "").replace(".0", "");
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue + "".trim();
    }
}
